"Gail Shaw" <initialsurname@sentech sa dot com> wrote in message 
news:471a7343@news.povray.org...
>
> "Orchid XP v7" <voi### [at] devnull> wrote in message
> news:471a5fde@news.povray.org...
>
>>
>> Yeah - and then rebuild all the table structures, indexes, data
>> permissions, etc. :-S Still, it depends on how "vital" the data is. I
>> take it you didn't just loose your entire customer list or anything? ;-)
>
> Not necessary. Do a select * from all the system tables too, or generate 
> the
> scripts straight from the system tables Is possible at least in MS SQL. Is
> something that I do fairly often when creting a replica of the prod
> environment
>
> Still that is the last resort. Should always be able to revert back to a
> backup.

Yep it is possible and I may have a go at it.  Oracle Enterprise Manager is 
much more work to set up than the Recovery Manager.  Although I was earlier 
testing out the export on RMAN, OEM is more critical to preserve because of 
the amount of work redoing it.

RMAN has features that make life easier recovering from this situation: 
When a database physical backup happens, as well as storing its backup job 
info in the RMAN catalog, RMAN also stores it in the client DB's own 
controlfile, which is part of the physical structure of the DB.  So the 
database contains its own recovery information and backups do still happen 
even with the catalog lost, and it does all get sent to tape eventually. 
Secondly, Oracle version 10g (three DBs are 10g RMAN clients here) has a 
cool, easy feature:  In one command, all the physical files in the backup 
storage area are scanned, type-identified, and any orphan backups that are 
unknown to the catalog can be added into it.  It is easy to build a new 
empty catalog and then populate it with data from prior backups.

I expect much more trouble with preserving OEM objects.  I haven't looked in 
detail, but I expect it has a complex schema.  It would be best if I use an 
AppDev tool to pull all the definitions for all objects, because it would 
take much study and work to make a roll-your-own SQL script that reads the 
data dictionary and dynamically writes user object build scripts based on 
what it finds.

Selecting from tables, their column definitions and table data are only a 
small part of getting the data and structure out:  I have to get the entire 
application out.  I would also have to make SQL to recreate:

Table type (heap, index-organised, cluster, partition) Definitions of all 
indexes and constraints on each table, foreign-key constraints to other 
tables, tablespace and storage info, remembering again to do the same for 
each of its indexes.

Triggers on the tables -- PL/SQL code that fires before/after an 
insert/update/delete.
Sequences, including the number they are now at. (So new sequences are 
created from that starting point instead of starting at one)

Views -- named SQL that can be selected and joined as one uses a table in 
most places.
The program code stored in PL/SQL functions, procedures and packages.

Jobs.  Ugh, I know the least about these.  I'm sure OEM will have job 
schedules registered in the database engine.

I already have a script that dynamically builds a bunch of creation SQL 
scripts for such things as users (also preserving passwords by copying the 
encrypted value), roles, grants, quotas.

Actually, the more I think about it, the more I think that the normal 
procedure of building Enterprise Manager and discovering all the target 
Oracle DBs, listeners, etc, setting up the administrators, alarm triggers, 
notification pagers and emails -- bad as it is, it may be easier and more 
reliable in the end.  Certainly the final result would have vendor support, 
whereas one I made with manually-pulled data wouldn't, and you could never 
be sure that what you made works 100% kosher and won't blow up on you later 
or let you down in an emergency.

We will have an export of the OEM schema.  It is just oldish because it is 
before the exports began to fail.  I would try loading that before doing a 
full rebuild or manual script-magic-blue-smoke anyway.

-- 
Cheers,
  Brian

Post a reply to this message